Database Tutorials Window Functions এবং Recursive Queries গাইড ও নোট

PostgreSQL এর কিছু শক্তিশালী বৈশিষ্ট্য রয়েছে, যেমন Window Functions এবং Recursive Queries। এই দুটি ফিচার ডেটাবেস কুয়েরি লেখার সময় বেশ কার্যকরী। নিচে এই দুটি বৈশিষ্ট্য বিস্তারিতভাবে আলোচনা করা হলো।

1. Window Functions

Window Functions হল এমন ধরনের SQL ফাংশন যা একটি উইন্ডো বা সেট অফ রো-এর উপর কাজ করে এবং প্রতিটি রো এর সাথে পরবর্তী রো বা আগের রো-এর তথ্য যুক্ত করে। এই ফাংশনগুলি সাধারণত OVER() ক্লজের সাথে ব্যবহার করা হয়। এটি বিশেষভাবে অ্যাগ্রিগেট ফাংশনের (যেমন, SUM(), AVG(), COUNT()) মধ্যে আরও সূক্ষ্ম বিশ্লেষণ বা বিশ্লেষণী ফাংশন প্রদান করতে ব্যবহৃত হয়, যেখানে পুরো টেবিলের জন্য একত্রিত ফলাফল না দিয়ে প্রতি রো এর উপর হিসাব করা হয়।

Window Functions এর সাধারণ ব্যবহার

ধরা যাক, আমাদের একটি sales টেবিল আছে, যেখানে প্রতিটি বিক্রির তথ্য রয়েছে:

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    salesperson_id INT,
    sale_amount DECIMAL,
    sale_date DATE
);

এখন, যদি আমরা প্রতিটি বিক্রির সাথে মোট বিক্রির পরিমাণ দেখতে চাই, তবে window function ব্যবহার করে আমরা এই কাজটি করতে পারি।

Example: Total Sales per Saleperson

SELECT 
    salesperson_id, 
    sale_amount,
    SUM(sale_amount) OVER (PARTITION BY salesperson_id) AS total_sales
FROM 
    sales;

ব্যাখ্যা:

• এখানে, SUM(sale_amount) একটি window function যা salesperson_id অনুযায়ী পার্টিশনিং করে প্রতিটি বিক্রির জন্য মোট বিক্রির পরিমাণ হিসাব করে।
• PARTITION BY salesperson_id নির্দেশ করে যে, প্রতিটি বিক্রির জন্য মোট বিক্রির পরিমাণ একই salesperson_id এর ভিত্তিতে হিসাব করা হবে।

Window Functions এর অন্য কিছু উদাহরণ

• ROW_NUMBER(): রেকর্ডের জন্য একটি সিকোয়েন্সিয়াল নম্বর (Row number) প্রদান করে।

  SELECT 
      salesperson_id, 
      sale_amount, 
      ROW_NUMBER() OVER (ORDER BY sale_date) AS row_num
  FROM 
      sales;
  

• RANK(): রেকর্ডের মধ্যে র‌্যাঙ্কিং প্রদান করে। সমান মানের রেকর্ডের জন্য সমান র‌্যাঙ্ক হয় এবং পরবর্তী র‌্যাঙ্কটি "স্কিপ" হয়।

  SELECT 
      salesperson_id, 
      sale_amount, 
      RANK() OVER (ORDER BY sale_amount DESC) AS rank
  FROM 
      sales;
  

2. Recursive Queries

Recursive Queries PostgreSQL-এ WITH RECURSIVE ব্যবহারের মাধ্যমে করা যায়। এই ধরনের কুয়েরি মূলত নিজের উপর কাজ করে এবং এটি ডেটাবেসের মধ্যে হায়ারার্কিক্যাল (পদানুসারে) সম্পর্কের জন্য খুবই উপযোগী। সাধারণত ট্রি ডাটা (যেমন, অর্গানাইজেশনাল চার্ট, ফোল্ডার স্ট্রাকচার) বা গ্রাফ সম্পর্কিত তথ্য (যেমন, পিতা-পুত্র সম্পর্ক) দেখার জন্য রিকার্সিভ কুয়েরি ব্যবহার করা হয়।

Recursive Query এর সাধারণ কাঠামো

WITH RECURSIVE recursive_cte AS (
    -- Base query (anchor member)
    SELECT 
        id, 
        parent_id, 
        name 
    FROM 
        categories
    WHERE 
        parent_id IS NULL

    UNION ALL

    -- Recursive query (recursive member)
    SELECT 
        c.id, 
        c.parent_id, 
        c.name
    FROM 
        categories c
    JOIN 
        recursive_cte r 
    ON 
        r.id = c.parent_id
)
SELECT * FROM recursive_cte;

ব্যাখ্যা:

• Base query (anchor member): প্রথমে আপনি বেস কেস নির্বাচন করেন, যেখানে আপনি মূল বা শীর্ষ স্তরের তথ্য চান। যেমন এখানে, আমরা parent_id IS NULL শর্ত দিয়ে প্রথম স্তরের ডেটা নির্বাচন করছি।
• Recursive query (recursive member): এরপর UNION ALL ব্যবহারের মাধ্যমে পরবর্তী স্তরের ডেটা নির্বাচন করি। এখানে, আমরা পূর্বের রেকর্ডের id কে পরবর্তী স্তরের parent_id হিসেবে ব্যবহার করছি।
• Recursive CTE (Common Table Expression): এই অংশটি রিকার্সিভ কুয়েরি একত্রিত করার জন্য ব্যবহৃত হয়।

উদাহরণ: অর্গানাইজেশনাল চার্টের ডেটা

ধরা যাক, আমাদের একটি employees টেবিল রয়েছে যা কর্মচারীদের এবং তাদের ব্যবস্থাপকদের সম্পর্ক দেখায়:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);

এখন, যদি আমরা একটি কর্মচারীর অধীনস্থ সকল কর্মচারীর তথ্য দেখতে চাই, আমরা একটি রিকার্সিভ কুয়েরি ব্যবহার করতে পারি।

WITH RECURSIVE employee_hierarchy AS (
    SELECT 
        id, 
        name, 
        manager_id
    FROM 
        employees
    WHERE 
        manager_id IS NULL   -- Starting with the top-level manager (root)

    UNION ALL

    SELECT 
        e.id, 
        e.name, 
        e.manager_id
    FROM 
        employees e
    JOIN 
        employee_hierarchy eh 
    ON 
        eh.id = e.manager_id   -- Recursively joining the manager-employee relation
)
SELECT * FROM employee_hierarchy;

ব্যাখ্যা:

• এই কুয়েরি আমাদের কর্মচারীদের এবং তাদের ম্যানেজারের মধ্যে সম্পর্ক তৈরি করবে, যেখানে প্রথমে শীর্ষস্তরের কর্মচারী (ম্যানেজার) নির্বাচন করা হয় এবং তারপরে তার অধীনস্থ কর্মচারীদের পুনরায় নির্বাচন করা হয়।

সারাংশ

• Window Functions PostgreSQL-এ ডেটাবেস কুয়েরি বিশ্লেষণ করার জন্য ব্যবহৃত হয়। এটি একটি উইন্ডো বা রো-এর সেটের উপর কাজ করে এবং বিশেষভাবে অ্যাগ্রিগেট ফাংশন এবং রাঙ্কিং-এর জন্য ব্যবহৃত হয়।
• Recursive Queries PostgreSQL-এ WITH RECURSIVE কুয়েরি ব্যবহারের মাধ্যমে করা হয়, যা মূলত ডেটাবেসের মধ্যে হায়ারার্কিক্যাল সম্পর্ক (যেমন পিতা-পুত্র বা অর্গানাইজেশনাল চার্ট) বিশ্লেষণ করতে ব্যবহৃত হয়।

এই দুটি ফিচার PostgreSQL-এ শক্তিশালী কুয়েরি লেখার ক্ষমতা প্রদান করে এবং বড় এবং জটিল ডেটা সেটের জন্য কার্যকরী বিশ্লেষণ করতে সহায়তা করে।